﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class THUCDONDAO
    {
        //AI LÀM THÌ ĐÓNG KHUNG PHẦN CỦA MÌNH VÀO NHA. CÁC CHÚ NHỚ SAO XÓA LỘN XÌ NGẦU LÊN HẾT
        ////////////////////////////CỦA LINH/////////////////////////
        public List<THUCDONDTO> LayDSMon(LOAIMONDTO LDTO, BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            //Lấy danh sách món chưa gọi
            SqlCommand command = new SqlCommand("LINH23_DANHSACHMONCHUAGOI", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int);
            command.Parameters["@MaBan"].Value = BDTO.MaBan;

            command.Parameters.Add("@MaLoai", System.Data.SqlDbType.Int);
            command.Parameters["@MaLoai"].Value = LDTO.MaLoai;

            SqlDataReader reader = command.ExecuteReader();
            List<THUCDONDTO> ds = new List<THUCDONDTO>();
            while (reader.Read())
            {
                THUCDONDTO TDTO = new THUCDONDTO();
                TDTO.MaMon = reader.GetInt32(0);
                TDTO.TenMon = reader.GetString(1);
                TDTO.SL= 0;
                TDTO.KM = 0;
                TDTO.DVT = reader.GetString(2);
                ds.Add(TDTO);
            }
            //Lấy danh sách món đã gọi
            reader.Close();
            command = new SqlCommand("LINH22_DANHSACHMONDAGOI", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int);
            command.Parameters["@MaBan"].Value = BDTO.MaBan;

            command.Parameters.Add("@MaLoai", System.Data.SqlDbType.Int);
            command.Parameters["@MaLoai"].Value = LDTO.MaLoai;
            
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                THUCDONDTO TDTO = new THUCDONDTO();
                TDTO.MaMon = reader.GetInt32(0);
                TDTO.TenMon = reader.GetString(1);
                TDTO.SL = reader.GetInt32(2);
                TDTO.KM = reader.GetInt32(3);
                TDTO.DVT = reader.GetString(4);
                ds.Add(TDTO);
            }
            conn.Close();
            return ds;
        }
        public List<THUCDONDTO> DSMon(LOAIMONDTO LDTO, DateTime bd, DateTime kt)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("LINH39_MONDABAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaLoai", System.Data.SqlDbType.Int);
            command.Parameters["@MaLoai"].Value = LDTO.MaLoai;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            SqlDataReader reader = command.ExecuteReader();
            List<THUCDONDTO> ds = new List<THUCDONDTO>();
            while (reader.Read())
            {
                THUCDONDTO TDTO = new THUCDONDTO();
                TDTO.TenMon = reader.GetString(0);
                TDTO.DVT = reader.GetString(1);
                TDTO.SL = reader.GetInt32(2);
                TDTO.KM = reader.GetInt32(3);
                TDTO.Tong = reader.GetInt32(4);
                ds.Add(TDTO);
            }
            conn.Close();
            return ds;
        }
        public THUCDONDTO TongMon(LOAIMONDTO LDTO, DateTime bd, DateTime kt)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("LINH40_TONGMONDABAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaLoai", System.Data.SqlDbType.Int);
            command.Parameters["@MaLoai"].Value = LDTO.MaLoai;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            SqlDataReader reader = command.ExecuteReader();
               
            THUCDONDTO TDTO = new THUCDONDTO();
            if (reader.Read())
            {
                TDTO.SL = reader.GetInt32(0);
                TDTO.KM = reader.GetInt32(1);
                TDTO.Tong = reader.GetInt32(2);
            }
            conn.Close();
            return TDTO;
        }
        public THUCDONDTO LayMaLoaiMon(THUCDONDTO TDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            //Lấy danh sách món chưa gọi
            SqlCommand command = new SqlCommand("LINH32_LAYMALOAIMON", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaMon", System.Data.SqlDbType.Int);
            command.Parameters["@MaMon"].Value = TDTO.MaMon;

            SqlDataReader reader = command.ExecuteReader();
            THUCDONDTO TDDTO = new THUCDONDTO();
            if (reader.Read())
            {
                //ten soluong dvt dongia khuyenmai
                TDDTO.MaLoai = reader.GetInt32(0);
                TDDTO.TenLoai = reader.GetString(1);
                TDDTO.MaMon = reader.GetInt32(2);
                TDDTO.TenMon = reader.GetString(3);
                TDDTO.DVT = reader.GetString(4);
            }
            conn.Close();
            return TDDTO;
        }
        public string LayDVT(THUCDONDTO MDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            //Lấy danh sách món chưa gọi
            SqlCommand command = new SqlCommand("LINH26_LAYDVT", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaMon", System.Data.SqlDbType.Int);
            command.Parameters["@MaMon"].Value = MDTO.MaMon;
            string s = command.ExecuteScalar().ToString();
            conn.Close();
            return s;
        }
        //////////////////////////////////////////////////////////////
        public List<THUCDONDTO> LayDSDVT()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("LayDSDVT", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = cmd.ExecuteReader();
            List<THUCDONDTO> ds = new List<THUCDONDTO>();
            while (reader.Read())
            {
                THUCDONDTO tdDTO = new THUCDONDTO();
                tdDTO.DVT = reader.GetString(0);
                ds.Add(tdDTO);
            }
            conn.Close();
            return ds;
        }
        public int ThemMon(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("ThemMon", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.Text).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.Text).Value = tdDTO.DVT;
            cmd.Parameters.Add("@dongia", System.Data.SqlDbType.Money).Value = tdDTO.DonGia;
            cmd.Parameters.Add("@maloai", System.Data.SqlDbType.Int).Value = tdDTO.MaLoai;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public DataTable LayDSThucDon()
        {
            DataTable tb = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("LayDSThucDon", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(tb);

            
            conn.Close();
            return tb;
        }
        public DataTable TimMon(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("TimMon", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.NVarChar).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.NVarChar).Value = tdDTO.DVT;
            cmd.Parameters.Add("@dongia", System.Data.SqlDbType.Money).Value = tdDTO.DonGia;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            DataTable tb = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(tb);
           
            conn.Close();
            return tb;
        }
        public int XoaMon(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XoaMon", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.Text).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.Text).Value = tdDTO.DVT;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public int SuaMon(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("SuaMon", conn);
            cmd.Parameters.Add("@tenmoncu", System.Data.SqlDbType.Text).Value = tdDTO.TenMonCu;
            cmd.Parameters.Add("@dvtcu", System.Data.SqlDbType.Text).Value = tdDTO.DVTCu;
            cmd.Parameters.Add("@tenmonmoi", System.Data.SqlDbType.Text).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvtmoi", System.Data.SqlDbType.Text).Value = tdDTO.DVT;
            cmd.Parameters.Add("@dongiamoi", System.Data.SqlDbType.Money).Value = tdDTO.DonGia;
            cmd.Parameters.Add("@maloai", System.Data.SqlDbType.Int).Value = tdDTO.MaLoai;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            else
                return 0;
        }
        public int DoiTinhTrang(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("DoiTinhTrang", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.Text).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.Text).Value = tdDTO.DVT;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            if (kq > 0)
                return 1;
            else return 0;
        }
        public DataTable LayDSThucDonDaXoa()
        {
            DataTable tb = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("LayDSThucDonDaXoa", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(tb);

            conn.Close();
            return tb;
        }
        public int PhucHoiThucDonDaXoa(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("PhucHoiThucDonDaXoa", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.NVarChar, 50).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.NVarChar, 50).Value = tdDTO.DVT;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            return 0;
        }
        public int XoaThatSuThucDon(THUCDONDTO tdDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand("XoaThatSuThucDon", conn);
            cmd.Parameters.Add("@tenmon", System.Data.SqlDbType.NVarChar, 50).Value = tdDTO.TenMon;
            cmd.Parameters.Add("@dvt", System.Data.SqlDbType.NVarChar, 50).Value = tdDTO.DVT;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq > 0)
                return 1;
            return 0;
        }
    }
}
